import xlwings as xw
from tkinter import Tk
from tkinter.filedialog import askopenfilename, asksaveasfilename
from PIL import ImageGrab
import os

def transfer_contents():
    # Create a Tkinter root window
    root = Tk()
    root.withdraw()  # Hide the root window

    try:
        # Ask for the source file
        source_file = askopenfilename(title="Select the source Excel file")
        if not source_file:
            print("No source file selected. Exiting...")
            return

        # Ask for the destination file
        destination_file = asksaveasfilename(title="Select the destination Excel file")
        if not destination_file:
            print("No destination file selected. Exiting...")
            return

        # Connect to the source Excel file
        source_app = xw.App(visible=False)
        source_book = source_app.books.open(source_file)

        # Connect to the destination Excel file
        destination_app = xw.App(visible=False)
        destination_book = destination_app.books.open(destination_file)

        # Iterate over all sheets in the source workbook
        for source_sheet in source_book.sheets:
            source_sheet_name = source_sheet.name
            print(f"Transferring contents for sheet: {source_sheet_name}")

            # Find or create the corresponding sheet in the destination workbook
            try:
                destination_sheet = destination_book.sheets[source_sheet_name]
            except Exception as e:
                print(f"Error: Sheet '{source_sheet_name}' not found in destination workbook. Skipping...")
                continue

            # Transfer cell values
            used_range = source_sheet.used_range
            for row in range(used_range.row, used_range.last_cell.row + 1):
                for column in range(used_range.column, used_range.last_cell.column + 1):
                    source_cell = source_sheet.cells(row, column)
                    destination_cell = destination_sheet.cells(row, column)
                    if (
                        # not destination_cell.api.Locked
                        # and not destination_cell.api.FormulaHidden
                        not source_cell.value is None 
                        and not destination_cell.api.Locked 
                        and not destination_cell.api.FormulaHidden
                    ):
                        destination_cell.value = source_cell.value
                        #print(source_cell.value)

            # Transfer images
            source_pictures = source_sheet.pictures
            destination_pictures = destination_sheet.pictures
            for destination_picture in destination_pictures:
                destination_picture.delete()

            for source_picture in source_pictures:
                print(f'Picture with name: {source_picture.api.Name} found')
                picture_top     = source_picture.top
                picture_left    = source_picture.left
                picture_width   = source_picture.width
                picture_height  = source_picture.height
                picture_name    = source_picture.api.Name
                
                source_picture.api.Copy()
                
                img = ImageGrab.grabclipboard()
                img.save('pic_to_transfer.png')

                destination_sheet.pictures.add(
                    image=os.path.join(os.getcwd(),'pic_to_transfer.png'),
                    left=picture_left,
                    top=picture_top,
                    width=picture_width,
                    height=picture_height,
                    name=picture_name
                    )
                os.remove('pic_to_transfer.png')
        # Save and close the destination workbook
        destination_book.save()
        destination_book.close()

        # Close the source workbook
        source_book.close()

        print("Contents transferred successfully!")

    except Exception as e:
        print(f"An error occurred: {str(e)}")
    finally:
        try:
            # Quit the Excel applications
            source_app.quit()
            destination_app.quit()
        except Exception:
            # If quit fails, kill the Excel processes
            source_app.kill()
            destination_app.kill()
        # Release the COM objects to avoid memory leaks


if __name__ == "__main__":
    # Run the script
    transfer_contents()
